﻿using H2.NET.Web.API.Common;
using H2.NET.Web.API.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;

namespace H2.NET.Web.API.Service
{
    public class DBService
    {
        #region 保存CRM推送过来的数据到数据库
        public static bool SaveData(List<SmdSync> syncList)
        {
            bool result = false;
            try
            {
                foreach (var syncModel in syncList)
                {
                    syncModel.SyncStatus = Convert.ToInt32(SyncStatus.Intial);
                    syncModel.SyncCreateDate = DateTime.Now;
                    syncModel.ID = Guid.NewGuid();
                    Type type = syncModel.GetType();
                    StringBuilder filedSB = new StringBuilder();
                    StringBuilder valueSB = new StringBuilder();
                    List<SqlParameter> pars = new List<SqlParameter>();


                    //通过GetProperties方法可以获取该类下的所有成员
                    foreach (PropertyInfo pi in type.GetProperties())
                    {
                        string nameStr = pi.Name;
                        var value = pi.GetValue(syncModel, null);

                        if (value != null && Validationcs.SyncValidate(value))
                        {
                            filedSB.Append(nameStr + ", ");
                            valueSB.Append("@" + nameStr + ", ");
                            //if (nameStr.ToLower() == "id")
                            //{
                            //    pars.Add(new SqlParameter("@" + nameStr, Guid.NewGuid()));
                            //}
                            //else if(nameStr.ToLower() == "syncstatus")
                            //{
                            //    pars.Add(new SqlParameter("@" + nameStr, 1));
                            //}
                            //else
                            //{
                            //    pars.Add(new SqlParameter("@" + nameStr, value));
                            //} 
                            #region 特殊字段赋值
                            if (nameStr.ToLower() == "storesize" || nameStr.ToLower() == "buymachinedeposit" || nameStr.ToLower() == "machinedeposit" || nameStr.ToLower() == "totaldeposit" || nameStr.ToLower() == "lat" || nameStr.ToLower() == "lon")
                            {
                                decimal buymachinedeposit;
                                if (!decimal.TryParse(value.ToString(), out buymachinedeposit))
                                {
                                    //Logging.GetLogger().Debug(nameStr + " " + value);
                                }
                                pars.Add(new SqlParameter("@" + nameStr, buymachinedeposit));
                            }
                            else if (nameStr.ToLower() == "submitdate" || nameStr.ToLower() == "opendate" || nameStr.ToLower() == "closedwndate" || nameStr.ToLower() == "create_time" || nameStr.ToLower() == "handletime" || nameStr.ToLower() == "lastupdate_time" || nameStr.ToLower() == "lastupdate_time")
                            {
                                DateTime closedwndate = new DateTime();
                                if (!DateTime.TryParse(value.ToString(), out closedwndate))
                                {
                                    //Logging.GetLogger().Debug(nameStr + " " + value);
                                }
                                pars.Add(new SqlParameter("@" + nameStr, closedwndate));
                            }
                            else if (nameStr.ToLower() == "ownertype")
                            {
                                int ownertype = -1;
                                if (!Int32.TryParse(value.ToString(), out ownertype))
                                {
                                    //Logging.GetLogger().Debug(nameStr + " " + value);
                                }
                                pars.Add(new SqlParameter("@" + nameStr, ownertype));
                            }
                            #endregion
                            else
                            {
                                pars.Add(new SqlParameter("@" + nameStr, value));
                            }
                            Logging.GetLogger().Debug(nameStr + " " + value);
                        }
                    }


                    string fieldStr = filedSB.ToString().Substring(0, filedSB.ToString().LastIndexOf(','));

                    string valueStr = valueSB.ToString().Substring(0, valueSB.ToString().LastIndexOf(','));

                    StringBuilder sqlSB = new StringBuilder();
                    sqlSB.Append("insert into t_smd_SmdSyncData(");
                    sqlSB.Append(fieldStr + ") values(");
                    sqlSB.Append(valueStr + "); ");

                    Logging.GetLogger().Debug(sqlSB.ToString());
                    int count = DBHelper.Instance.Execute(sqlSB.ToString(), pars.ToArray(), System.Data.CommandType.Text);

                    if (count < 1)
                    {
                        return false;
                    }
                    else
                    {
                        result = true;
                    }
                }
            }
            catch (Exception ex)
            {
                Logging.GetLogger().Error("方法 SaveData(保存Sync数据) 出错：" + ex);
                return false;
            }

            return result;
        }
        #endregion

        #region 更新保存分销商信息 --Add By Tony 20160615

        #region 保存分销商信息
        public static bool SaveSubDealer(List<SubDealerModel> subdealerList)
        {
            bool result = false;
            try
            {
                foreach (var subdealer in subdealerList)
                {
                    if (subdealer != null && !string.IsNullOrEmpty(subdealer.DealerCode))
                    {
                        if (IfExistSubDealer(subdealer.DealerCode))
                        {
                            if (UpdateSubDealer(subdealer))
                            {
                                result = true;
                            }
                            else
                            {
                                return false;
                            }
                        }
                        else
                        {
                            if (InsertSubDealer(subdealer))
                            {
                                result = true;
                            }
                            else
                            {
                                return false;
                            }
                        }
                    }
                    else
                    {
                        continue;
                    }


                }
                return result;
            }
            catch (Exception ex)
            {
                Logging.GetLogger().Error(string.Format("SaveSubDealer()错误：{0}", ex.Message));
                return result;
            }
        }
        #endregion

        #region 如果不存在的话插入新的分销商信息
        private static bool InsertSubDealer(SubDealerModel subdealer)
        {
            bool result = false;

            Type type = subdealer.GetType();

            StringBuilder filedSB = new StringBuilder();
            StringBuilder valueSB = new StringBuilder();
            List<SqlParameter> pars = new List<SqlParameter>();

            //通过GetProperties方法可以获取该类下的所有成员
            foreach (PropertyInfo pi in type.GetProperties())
            {
                string nameStr = pi.Name;
                var value = pi.GetValue(subdealer, null);

                if (value != null && Validationcs.SyncValidate(value))
                {
                    filedSB.Append(nameStr + ", ");
                    valueSB.Append("@" + nameStr + ", ");

                    pars.Add(new SqlParameter("@" + nameStr, value));

                    Logging.GetLogger().Debug(nameStr + " " + value);
                }
            }
            string fieldStr = filedSB.ToString().Substring(0, filedSB.ToString().LastIndexOf(','));

            string valueStr = valueSB.ToString().Substring(0, valueSB.ToString().LastIndexOf(','));

            StringBuilder sqlSB = new StringBuilder();
            sqlSB.Append("insert into t_smd_dealer(");
            sqlSB.Append(fieldStr + ") values(");
            sqlSB.Append(valueStr + "); ");

            int count = DBHelper.Instance.Execute(sqlSB.ToString(), pars.ToArray(), System.Data.CommandType.Text);
            if (count > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion

        #region 如果原先库里已经存在这个分销商的话则更新原来的信息
        private static bool UpdateSubDealer(SubDealerModel subdealer)
        {
            bool result = false;
            Type type = subdealer.GetType();

            StringBuilder filedSB = new StringBuilder();
            StringBuilder valueSB = new StringBuilder();
            List<SqlParameter> pars = new List<SqlParameter>();
            //通过GetProperties方法可以获取该类下的所有成员
            foreach (PropertyInfo pi in type.GetProperties())
            {
                string nameStr = pi.Name;
                var value = pi.GetValue(subdealer, null);

                if (value != null && Validationcs.SyncValidate(value))
                {
                    filedSB.Append(nameStr + " = @" + nameStr + ",");

                    pars.Add(new SqlParameter("@" + nameStr, value));

                    Logging.GetLogger().Debug(nameStr + " " + value);
                }
            }

            string fieldStr = filedSB.ToString().Substring(0, filedSB.ToString().LastIndexOf(','));

            StringBuilder sqlSB = new StringBuilder();
            sqlSB.Append("update t_smd_dealer set ");
            sqlSB.Append(fieldStr + " where dealercode =@DealerCode");

            int count = DBHelper.Instance.Execute(sqlSB.ToString(), pars.ToArray(), System.Data.CommandType.Text);
            if (count > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion

        #region 判断是否已经存在该分销商记录
        private static bool IfExistSubDealer(string dealerCode)
        {
            bool result = false;
            string sqlStr = "select * from t_smd_dealer where dealercode = @dealercode";
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@dealercode", dealerCode));
            DataSet ds = DBHelper.Instance.ExcuteQuery(sqlStr, pars.ToArray(), System.Data.CommandType.Text);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion

        #endregion

        #region 更新保存BMC信息 --Add By Tony 20160615
        #region 保存BMC信息
        public static bool SaveBMCInfo(List<BMCModel> bmcList)
        {
            bool result = false;
            try
            {
                foreach (var bmc in bmcList)
                {
                    if (bmc != null && !string.IsNullOrEmpty(bmc.BMCCode))
                    {
                        if (IfExistBMC(bmc.BMCCode))
                        {
                            if (UpdateBMC(bmc))
                            {
                                result = true;
                            }
                            else
                            {
                                return false;
                            }
                        }
                        else
                        {
                            if (InsertBMC(bmc))
                            {
                                result = true;
                            }
                            else
                            {
                                return false;
                            }
                        }
                    }
                    else
                    {
                        continue;
                    }


                }
                return result;
            }
            catch (Exception ex)
            {
                Logging.GetLogger().Error(string.Format("SaveBMCInfo()错误：{0}", ex.Message));
                return result;
            }
        }
        #endregion

        #region 如果原先库中不存在BMC信息，则插入新的BMC记录
        private static bool InsertBMC(BMCModel bmc)
        {
            bool result = false;

            Type type = bmc.GetType();

            StringBuilder filedSB = new StringBuilder();
            StringBuilder valueSB = new StringBuilder();
            List<SqlParameter> pars = new List<SqlParameter>();

            //通过GetProperties方法可以获取该类下的所有成员
            foreach (PropertyInfo pi in type.GetProperties())
            {
                string nameStr = pi.Name;
                var value = pi.GetValue(bmc, null);

                if (value != null && Validationcs.SyncValidate(value))
                {
                    filedSB.Append(nameStr + ", ");
                    valueSB.Append("@" + nameStr + ", ");

                    pars.Add(new SqlParameter("@" + nameStr, value));

                    Logging.GetLogger().Debug(nameStr + " " + value);
                }
            }
            string fieldStr = filedSB.ToString().Substring(0, filedSB.ToString().LastIndexOf(','));

            string valueStr = valueSB.ToString().Substring(0, valueSB.ToString().LastIndexOf(','));

            StringBuilder sqlSB = new StringBuilder();
            sqlSB.Append("insert into t_smd_bmcinfo(");
            sqlSB.Append(fieldStr + ") values(");
            sqlSB.Append(valueStr + "); ");

            int count = DBHelper.Instance.Execute(sqlSB.ToString(), pars.ToArray(), System.Data.CommandType.Text);
            if (count > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion

        #region 如果原来库中存在BMC信息，则更新原来的记录
        private static bool UpdateBMC(BMCModel bmc)
        {
            bool result = false;
            Type type = bmc.GetType();

            StringBuilder filedSB = new StringBuilder();
            StringBuilder valueSB = new StringBuilder();
            List<SqlParameter> pars = new List<SqlParameter>();
            //通过GetProperties方法可以获取该类下的所有成员
            foreach (PropertyInfo pi in type.GetProperties())
            {
                string nameStr = pi.Name;
                var value = pi.GetValue(bmc, null);

                if (value != null && Validationcs.SyncValidate(value))
                {
                    filedSB.Append(nameStr + " = @" + nameStr + ",");

                    pars.Add(new SqlParameter("@" + nameStr, value));

                    Logging.GetLogger().Debug(nameStr + " " + value);
                }
            }

            string fieldStr = filedSB.ToString().Substring(0, filedSB.ToString().LastIndexOf(','));

            StringBuilder sqlSB = new StringBuilder();
            sqlSB.Append("update t_smd_bmcinfo set ");
            sqlSB.Append(fieldStr + " where bmccode =@BMCCode");

            int count = DBHelper.Instance.Execute(sqlSB.ToString(), pars.ToArray(), System.Data.CommandType.Text);
            if (count > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion

        #region 是否存在BMC
        private static bool IfExistBMC(string bmcCode)
        {
            bool result = false;
            string sqlStr = "select * from t_smd_bmcinfo where bmccode = @bmccode";
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@bmccode", bmcCode));
            DataSet ds = DBHelper.Instance.ExcuteQuery(sqlStr, pars.ToArray(), System.Data.CommandType.Text);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion
        #endregion

        #region 更新保存AccountOwner信息 --Add By Tony 20160615
        #region 保存AccountOwner信息
        public static bool SaveAccountOwner(List<AccountOwnerModel> accountownerList)
        {
            bool result = false;
            try
            {
                foreach (var accountOwner in accountownerList)
                {
                    if (accountOwner != null && !string.IsNullOrEmpty(accountOwner.WindowsId))
                    {
                        if (IfExistAccount(accountOwner.WindowsId))
                        {
                            if (UpdateAccountOwner(accountOwner))
                            {
                                result = true;
                            }
                            else
                            {
                                return false;
                            }
                        }
                        else
                        {
                            if (InsertAccountOwner(accountOwner))
                            {
                                result = true;
                            }
                            else
                            {
                                return false;
                            }
                        }
                    }
                    else
                    {
                        continue;
                    }


                }
                return result;
            }
            catch (Exception ex)
            {
                Logging.GetLogger().Error(string.Format("SaveAccountOwner()错误：{0}", ex.Message));
                return result;
            }
        }
        #endregion

        #region 如果原先库中不存在AccountOwner信息，则插入新的AccountOwner记录
        private static bool InsertAccountOwner(AccountOwnerModel accountOwner)
        {
            bool result = false;

            Type type = accountOwner.GetType();

            StringBuilder filedSB = new StringBuilder();
            StringBuilder valueSB = new StringBuilder();
            List<SqlParameter> pars = new List<SqlParameter>();

            //通过GetProperties方法可以获取该类下的所有成员
            foreach (PropertyInfo pi in type.GetProperties())
            {
                string nameStr = pi.Name;
                var value = pi.GetValue(accountOwner, null);

                if (value != null && Validationcs.SyncValidate(value))
                {
                    filedSB.Append(nameStr + ", ");
                    valueSB.Append("@" + nameStr + ", ");

                    pars.Add(new SqlParameter("@" + nameStr, value));

                    Logging.GetLogger().Debug(nameStr + " " + value);
                }
            }
            string fieldStr = filedSB.ToString().Substring(0, filedSB.ToString().LastIndexOf(','));

            string valueStr = valueSB.ToString().Substring(0, valueSB.ToString().LastIndexOf(','));

            StringBuilder sqlSB = new StringBuilder();
            sqlSB.Append("insert into t_smd_accountowner(");
            sqlSB.Append(fieldStr + ") values(");
            sqlSB.Append(valueStr + "); ");

            int count = DBHelper.Instance.Execute(sqlSB.ToString(), pars.ToArray(), System.Data.CommandType.Text);
            if (count > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion

        #region 如果原来库中存在AccountOwner信息，则更新原来的记录
        private static bool UpdateAccountOwner(AccountOwnerModel accountOwner)
        {
            bool result = false;
            Type type = accountOwner.GetType();

            StringBuilder filedSB = new StringBuilder();
            StringBuilder valueSB = new StringBuilder();
            List<SqlParameter> pars = new List<SqlParameter>();
            //通过GetProperties方法可以获取该类下的所有成员
            foreach (PropertyInfo pi in type.GetProperties())
            {
                string nameStr = pi.Name;
                var value = pi.GetValue(accountOwner, null);

                if (value != null && Validationcs.SyncValidate(value))
                {
                    filedSB.Append(nameStr + " = @" + nameStr + ",");

                    pars.Add(new SqlParameter("@" + nameStr, value));

                    Logging.GetLogger().Debug(nameStr + " " + value);
                }
            }

            string fieldStr = filedSB.ToString().Substring(0, filedSB.ToString().LastIndexOf(','));

            StringBuilder sqlSB = new StringBuilder();
            sqlSB.Append("update t_smd_accountowner set ");
            sqlSB.Append(fieldStr + " where windowsid =@WindowsId");

            int count = DBHelper.Instance.Execute(sqlSB.ToString(), pars.ToArray(), System.Data.CommandType.Text);
            if (count > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion

        #region 是否存在AccountOwner
        private static bool IfExistAccount(string windowsId)
        {
            bool result = false;
            string sqlStr = "select * from t_smd_accountowner where windowsId = @WindowsId";
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@WindowsId", windowsId));
            DataSet ds = DBHelper.Instance.ExcuteQuery(sqlStr, pars.ToArray(), System.Data.CommandType.Text);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                result = true;
            }
            return result;
        }
        #endregion
        #endregion

    }
}